package DBUtil;
/*
* 数据库连接工具类
* */
import user.Users;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {
 
    private Statement st=null;
    private ResultSet rs=null;
    private PreparedStatement ps=null;
    public static Connection getConnection(){
        // 1，加载驱动类，反射
        Connection con=null;
      try {
          Class.forName("oracle.jdbc.OracleDriver");
          // 2，获取连接.
          con = DriverManager.getConnection(
                  "jdbc:oracle:thin:@localhost:1521:orcl1",
                  "system",
                  "ljmspace");
      } catch (ClassNotFoundException e) {
          e.printStackTrace();
      } catch (SQLException e) {
          e.printStackTrace();
      }
      return con;

    }
    public int insert(Users us) throws ClassNotFoundException, SQLException {

// 1，获取连接.
        int i=0;
       try {
           Connection con = getConnection();
// 2执行sqL语句
//2,1创建sqL语句
           String sql = "insert into J_USER values(SEQ_J_USER.nextval,?,?,1)";
           //2，2创建prepareStatement对象，执行 sqL语句
           ps = con.prepareStatement(sql);
           //3.给占位符进行赋值
           ps.setString(1, us.getUsername());
           ps.setString(2, us.getPassword());
           i = ps.executeUpdate();
           DBUtil.close(null,ps,con,null);
       }
       catch (SQLException e) {
           e.printStackTrace();
       }

        return i;
    }
    //查询
    public List<Users> result() throws ClassNotFoundException, SQLException {
        //0.创建集合
        List<Users> user=new ArrayList<>();
        //1，获取连接.
       try{ Connection con = getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from J_USER";
        st = con.createStatement();
        rs = st.executeQuery(sql1);
        while(rs.next()){
            int id=rs.getInt("id");
            String username1=rs.getString("username");
            String password1=rs.getString("password");
            int state=rs.getInt("state");
            Users user1=new Users(id,username1,password1,state);
            user.add(user1);
        }
        close(null,null,con,rs);
       }
       catch (SQLException e) {
           e.printStackTrace();
       }
        return user;
    }
    //删除
    public void delete(Users us)throws ClassNotFoundException, SQLException{
try {
    //1，获取连接.
    Connection con = getConnection();
// 2执行sqL语句
//2,1创建sqL语句
    String sql2 = "delete from J_USER where USERNAME=?";
    ps = con.prepareStatement(sql2);
    ps.setString(1, us.getUsername());
    int i = ps.executeUpdate();
    close(null,ps,con,null);
} catch (SQLException e) {
    e.printStackTrace();
}

    }
    public int update(Users us)throws ClassNotFoundException, SQLException{

        //1，获取连接.
        int i = 0;
       try {
           Connection con = getConnection();
// 2执行sqL语句
//2,1创建sqL语句
           String sql3 = "update J_USER set PASSWORD=? where USERNAME=?";
           ps = con.prepareStatement(sql3);
           ps.setString(1, us.getPassword());
           ps.setString(2, us.getUsername());
           i = ps.executeUpdate();
           DBUtil.close( null,ps,con,null);

       } catch (SQLException e) {
           e.printStackTrace();
       }
        return i;
    }

    public static void close(Statement st, PreparedStatement ps, Connection con, ResultSet rs) throws SQLException {
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(ps!=null){
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
   /* public static void main(String[] args) throws SQLException, ClassNotFoundException {
        UserDao userdao = new UserDao();
        //userdao.insert("jkl","894546");
        //userdao.result("aaa","123456");
       //userdao.result("jkl", "894546");
        //userdao.delete("jkl","894546");
        userdao.update("aaa","111111");
    }*/
}

